﻿using System;
using System.Data.Odbc;
using System.Data;
using Gencode.Common.Properties;

namespace Gencode.Common.SQL
{
	public class SimpleQuerry
	{
		static OdbcConnection m_odbcConnection = null;
		static int m_odbcTimeout = -1;

		/// <summary>
		/// Executes a single SQLString to the given conection in DatabaseConection.ODBCConection or this.ODBCConection
		/// </summary>
		/// <param name="sqlString">String to be executed in conection</param>
		/// <returns>False if an error occured</returns>
		public static bool Execute(string sqlString)
		{
			return Execute(sqlString, false);
		}
		/// <summary>
		/// Executes a single SQLString to the given conection in DatabaseConection.ODBCConection or this.ODBCConection
		/// </summary>
		/// <param name="sqlString">String to be executed in conection</param>
		/// <param name="log">logs the executed sql string</param>
		/// <returns>False if an error occured</returns>
		public static bool Execute(string sqlString, bool log)
		{
			if (log)
				Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL, sqlString);

			OdbcCommand cmd = null;
			try
			{
				cmd = new OdbcCommand(sqlString, ODBCConnection);
			}
			catch (OdbcException e)
			{
				Errorhandling.Logger.ToXML(e.Message.ToString());
				return false;
			}

			try
			{
				ODBCConnection.Open();
				cmd.ExecuteNonQuery();
			}
			catch (Exception objError)
			{
				Errorhandling.Logger.ToXML(objError.Message.ToString() + "\nQuery:\n" + sqlString);
				if (!log)
					Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL_ERROR, sqlString);
				Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL_ERROR, objError.Message.ToString());
				return false;
			}
			finally
			{
				cmd.Dispose();
				ODBCConnection.Close();
			}

			return true;
		}

		/// <summary>
		/// Executes a single SQLString to the given conection in DatabaseConection.ODBCConection or this.ODBCConection
		/// </summary>
		/// <param name="sqlString">String to be executed in conection</param>
		/// <returns>A DataView with the information from the Database.
		/// Empty if an error occurs</returns>
		public static DataView ExecuteForDataView(string sqlString)
		{
			return ExecuteForDataView(sqlString, false);
		}

		/// <summary>
		/// Executes a single SQLString to the given conection in DatabaseConection.ODBCConection or this.ODBCConection
		/// </summary>
		/// <param name="sqlString">String to be executed in conection</param>
		/// <param name="log">logs the executed sql string</param>
		/// <returns>A DataView with the information from the Database.
		/// Empty if an error occurs</returns>
		public static DataView ExecuteForDataView(string sqlString, bool log)
		{
			DataView dv;
			DataSet ds = new DataSet();
			OdbcCommand com = null;

			if (log)
				Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL, sqlString);
			try
			{
				if (ODBCConnection.State == ConnectionState.Closed)
					OpenConnection();

				com = new OdbcCommand(sqlString, ODBCConnection);
				if (OdbcTimeout >= 0)
					com.CommandTimeout = OdbcTimeout;
				//cmd.SelectCommand.Connection.ConnectionTimeout = OdbcTimeout;

			}
			catch (OdbcException e)
			{
				Errorhandling.Logger.ToXML(e.Message.ToString() + "\nQuery:\n" + sqlString);
				if (!log)
					Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL_ERROR, sqlString);
				Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL_ERROR, e.Message.ToString());
				return new DataView();
			}

			try
			{
				//cmd.Fill(ds, "Daten");
				//dv = new DataView(ds.Tables["Daten"]);

				DataReaderAdapter dra = new DataReaderAdapter();
				DataTable dt = new DataTable();

				OdbcDataReader reader = com.ExecuteReader();
				dra.FillFromReader(dt, reader);
				dv = new DataView(dt);
				//????reader.Read();
				//????dv = ConvertToDataView(reader, "Data");

				reader.Close();
			}
			catch (OdbcException exp)
			{
				ds.Dispose();
				dv = null;
				//TODO:
				// Make Option for this
				//Errorhandling.Logger.ToXML(Resources.ODBCFehler + "\n" + exp.Message.ToString(), "", exp.Message.ToString());
				Errorhandling.Logger.ToXML(exp.Message.ToString());
				if (log)
					Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL, sqlString);
			}
			catch (OutOfMemoryException exp)
			{
				ds.Dispose();
				dv = null;
				//TODO:
				// Make Option for this
				//Errorhandling.Logger.ToXML(Resources.AbfrageZuGross1 + "\n" + Resources.AbfragezuGross2, "", exp.Message.ToString());
				Errorhandling.Logger.ToXML(exp.Message.ToString());
			}
			finally
			{
				ODBCConnection.Close();
				
				//cmd.Dispose();
				com.Dispose();
				ds.Dispose();
			}
			return dv;
		}

        /// <summary>
        /// Executes a single SQLString to the given conection in DatabaseConection.ODBCConection or this.ODBCConection
        /// </summary>
        /// <param name="sqlString">String to be executed in conection</param>
        /// <param name="log">logs the executed sql string</param>
        /// <returns>A DataView with the information from the Database.
        /// Empty if an error occurs</returns>
        public static DataTable ExecuteForDataTable(string sqlString, bool log = false)
        {
            DataTable dt = null;
            DataSet ds = new DataSet();
            OdbcCommand com = null;

            if (log)
                Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL, sqlString);
            try
            {
                if (ODBCConnection.State == ConnectionState.Closed)
                    OpenConnection();

                com = new OdbcCommand(sqlString, ODBCConnection);
                if (OdbcTimeout >= 0)
                    com.CommandTimeout = OdbcTimeout;
                //cmd.SelectCommand.Connection.ConnectionTimeout = OdbcTimeout;

            }
            catch (OdbcException e)
            {
                Errorhandling.Logger.ToXML(e.Message.ToString() + "\nQuery:\n" + sqlString);
                if (!log)
                    Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL_ERROR, sqlString);
                Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL_ERROR, e.Message.ToString());
                return null;
            }

            try
            {
                //cmd.Fill(ds, "Daten");
                //dv = new DataView(ds.Tables["Daten"]);

                DataReaderAdapter dra = new DataReaderAdapter();
                dt = new DataTable();

                OdbcDataReader reader = com.ExecuteReader();
                dra.FillFromReader(dt, reader);

                reader.Close();
            }
            catch (OdbcException exp)
            {
                ds.Dispose();
                //TODO:
                // Make Option for this
                //Errorhandling.Logger.ToXML(Resources.ODBCFehler + "\n" + exp.Message.ToString(), "", exp.Message.ToString());
                Errorhandling.Logger.ToXML(exp.Message.ToString());
                if (log)
                    Gencode.Common.ActionLogger.Logger.LoggMessage(Gencode.Common.ActionLogger.MessageType.SQL, sqlString);
            }
            catch (OutOfMemoryException exp)
            {
                ds.Dispose();
                //TODO:
                // Make Option for this
                //Errorhandling.Logger.ToXML(Resources.AbfrageZuGross1 + "\n" + Resources.AbfragezuGross2, "", exp.Message.ToString());
                Errorhandling.Logger.ToXML(exp.Message.ToString());
            }
            finally
            {
                ODBCConnection.Close();

                //cmd.Dispose();
                com.Dispose();
                ds.Dispose();
            }

            return dt;
        }

		private static DataView ConvertToDataView(OdbcDataReader setToCheck, string tableName)
		{
			DataTable dataReaderTable = new DataTable(tableName);
			try
			{
				for (int h = 0; h < setToCheck.FieldCount; h++)
				{
					DataColumn temp = new DataColumn(setToCheck.GetName(h), setToCheck.GetFieldType(h));
					dataReaderTable.Columns.Add(temp);
				}
				while (setToCheck.Read())
				{
					DataRow dr = dataReaderTable.NewRow();

					for (int g = 0; g < setToCheck.FieldCount; g++)
					{
						dr[g] = setToCheck.GetValue( setToCheck.GetOrdinal(setToCheck.GetName(g)));
					}
					dataReaderTable.Rows.Add(dr);
				}
				return dataReaderTable.DefaultView;
			}
			catch
			{
				return null;
			}
		}




		public static void OpenConnection()
		{
			if (ODBCConnection != null)
				ODBCConnection.Open();
		}

		public static void CloseConnection()
		{
			if (ODBCConnection != null)
				ODBCConnection.Close();
		}

		#region Properties
		/// <summary>
		/// OdbcConnection object
		/// </summary>
		public static OdbcConnection ODBCConnection
		{
			get
			{
				if (m_odbcConnection == null)
				{
					if (m_odbcTimeout >= 0)
						if (DatabaseConection.ODBCConnection != null)
							DatabaseConection.ODBCConnection.ConnectionTimeout = m_odbcTimeout;
					return DatabaseConection.ODBCConnection;
				}

				return m_odbcConnection;
			}
			set
			{
				if (m_odbcConnection != null)
				{
					m_odbcConnection.Dispose();
					m_odbcConnection = null;
				}

				m_odbcConnection = value;
				if (m_odbcTimeout >= 0)
					m_odbcConnection.ConnectionTimeout = m_odbcTimeout;
			}
		}

		public static int OdbcTimeout
		{
			get
			{
				return m_odbcTimeout;
			}
			set
			{
				m_odbcTimeout = value;

				if (ODBCConnection != null)
					ODBCConnection.ConnectionTimeout = value;
			}
		}

		#endregion
	}
}
